﻿/* 模块类型: 数据访问基础类 
 * 作者:     顾金翼
 * 日期:     2012/03/29
 * 功能描述: 数据访问模块(ADO.NET)
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace Perseus.WCF.DAL
{
    public class SqlDbUtil : IDisposable
    {
        private SqlConnection Connection;
        private String ConnectionString;

        public SqlDbUtil()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings[ApplicationConfig.DbConnection].ConnectionString;
        }
        public SqlDbUtil(string pStr)
        {
            ConnectionString = ConfigurationManager.ConnectionStrings[pStr].ConnectionString;
        }
        ~SqlDbUtil()
        {
            try
            {
                if (Connection != null)
                    Connection.Close();
            }
            catch { }
            try
            {
                Dispose();
            }
            catch { }
        }

        public SqlConnection pConnection
        {
            get
            {
                return Connection;
            }
            set
            {
                Connection = value;
            }
        }

        /// ***********************************
        /// Open Connection
        /// ***********************************
        public void Open()
        {
            if (Connection == null)
            {
                Connection = new SqlConnection(ConnectionString);
            }
            if (Connection.State.Equals(ConnectionState.Closed))
            {
                Connection.Open();
            }
        }
        /// ***********************************
        /// Close Connection
        /// ***********************************
        public void Close()
        {
            if (Connection != null)
                Connection.Close();
        }
        /// ***********************************
        /// Dispose Connection
        /// ***********************************
        public void Dispose()
        {
            // Ensure Connection Closed
            if (Connection != null)
            {
                Connection.Dispose();
                Connection = null;
            }
        }

        /// **********************************************************************
        /// Sql Executor
        /// <param name="SqlString">Sql语句</param>
        /// <returns>
        /// 对Update、Insert、Delete为影响到的行数，其他情况为-1
        /// </returns>
        /// **********************************************************************
        public int ExecuteSQL(String SqlString)
        {
            int count = -1;
            Open();
            try
            {
                SqlCommand cmd = new SqlCommand(SqlString, Connection);
                count = cmd.ExecuteNonQuery();
            }
            catch
            {
                count = -1;
            }
            finally
            {
                Close();
            }

            return count;
        }

        /// ***************************************************
        ///  Get Dataset By Stored Procedure No Param
        /// <return>DataSet</return>
        /// ***************************************************
        public DataSet GetDataSetBySp(string SpName)
        {
            Open();

            SqlDataAdapter da = new SqlDataAdapter(SpName, Connection);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet ds = new DataSet();
            da.Fill(ds);

            Close();

            return ds;
        }

        /// ***************************************************
        ///  Get Dataset By Stored Procedure With Params
        /// <param>SqlParameter[]</param>
        /// <return>DataSet</return>
        /// ***************************************************
        public DataSet GetDataSetBySpParam(string SpName, SqlParameter[] Prams)
        {
            Open();

            SqlDataAdapter da = new SqlDataAdapter(SpName, Connection);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter Param in Prams)
            {
                da.SelectCommand.Parameters.Add(Param);
            }

            DataSet ds = new DataSet();
            da.Fill(ds);

            Close();

            return ds;
        }

        /// ***************************************************
        /// 获取数据:返回一个DataSet
        /// <param name="SqlString">Sql语句</param>
        /// <returns>DataSet</returns>
        /// ***************************************************
        public DataSet GetDataSet(String SqlString)
        {
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(SqlString, Connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            Close();

            return dataset;
        }

        /// **************************************************
        /// 获取数据:返回一个SqlDataReader 
        /// （调用后主意调用SqlDataReader.Close()）
        /// <param name="SqlString">Sql语句</param>
        /// <returns>SqlDataReader</returns>
        /// **************************************************
        public SqlDataReader GetDataReader(String SqlString)
        {
            Open();
            SqlCommand cmd = new SqlCommand(SqlString, Connection);

            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }

        /// ***************************************************
        /// 获取数据:返回一个SqlDataReader 
        /// （调用后主意调用SqlDataReader.Close()、Database.Close()）
        /// <param name="SqlString">Sql语句</param>
        /// <returns>SqlDataReader</returns>
        /// ***************************************************
        public SqlDataReader GetSaDataReader(String SqlString)
        {
            Open();
            SqlCommand cmd = new SqlCommand(SqlString, Connection);

            return cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
        }

        /// ***************************************************
        /// 获取数据:返回一个DataTable
        /// <param name="SqlString">Sql语句</param>
        /// <returns>DataTable</returns>
        /// ***************************************************
        public DataTable GetDataTable(String SqlString)
        {
            DataSet dataset = GetDataSet(SqlString);
            dataset.CaseSensitive = false;

            return dataset.Tables[0];
        }

        /// ***************************************************
        /// 获取数据，返回一个DataRow
        /// <param name="SqlString">Sql语句</param>
        /// <returns>DataRow</returns>
        /// ***************************************************
        public DataRow GetDataRow(String SqlString)
        {
            DataSet dataset = GetDataSet(SqlString);
            dataset.CaseSensitive = false;

            if (dataset.Tables[0].Rows.Count > 0)
            {
                return dataset.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// ***************************************************
        /// 获取数据，返回一个DataRow
        /// <param name="SqlString">Sql语句</param>
        /// <param>SqlParameter[]</param>
        /// <returns>DataRow</returns>
        /// ***************************************************
        public DataRow GetDataRowBySql(string SqlString, SqlParameter[] Prams)
        {
            Open();

            SqlDataAdapter da = new SqlDataAdapter(SqlString, Connection);
            da.SelectCommand.CommandType = CommandType.Text;
            foreach (SqlParameter Param in Prams)
            {
                da.SelectCommand.Parameters.Add(Param);
            }

            DataSet ds = new DataSet();
            da.Fill(ds);
            ds.CaseSensitive = false;

            Close();

            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// ***************************************************
        /// 获取数据，返回一个DataRow
        /// <param>SqlParameter[]</param>
        /// <returns>DataRow</returns>
        /// ***************************************************
        public DataRow GetDataRowBySpParam(string SpName, SqlParameter[] Prams)
        {
            Open();

            SqlDataAdapter da = new SqlDataAdapter(SpName, Connection);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter Param in Prams)
            {
                da.SelectCommand.Parameters.Add(Param);
            }

            DataSet ds = new DataSet();
            da.Fill(ds);
            ds.CaseSensitive = false;

            Close();

            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }
        //Overload GetDataRowBySpParam
        public DataRow GetDataRowBySpParam(string SpName, SqlParameter[] Prams, int pNum)
        {
            Open();

            SqlDataAdapter da = new SqlDataAdapter(SpName, Connection);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter Param in Prams)
            {
                da.SelectCommand.Parameters.Add(Param);
            }

            DataSet ds = new DataSet();
            da.Fill(ds);
            ds.CaseSensitive = false;

            Close();

            if (ds.Tables[pNum].Rows.Count > 0)
            {
                return ds.Tables[pNum].Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// ***************************************************
        /// Execute a procedure of params
        /// <param>SqlParameter[]</param>
        /// <returns>是否成功</returns>
        /// ***************************************************
        public bool ExecuteSpParam(string SpName, SqlParameter[] Prams)
        {
            bool success = true;

            Open();

            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = Connection.BeginTransaction();
            cmd.Connection = Connection;
            cmd.Transaction = trans;
            cmd.CommandText = SpName;
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                foreach (SqlParameter pram in Prams)
                {
                    cmd.Parameters.Add(pram);
                }

                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                success = false;
                trans.Rollback();
            }
            finally
            {
                Close();
            }

            return success;
        }

        /// ***************************************************
        /// 执行一组Sql语句
        /// <param name="SqlStrings">Sql语句组</param>
        /// <returns>是否成功</returns>
        /// ***************************************************
        public bool ExecuteSQL(String[] SqlStrings)
        {
            bool success = true;

            Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = Connection.BeginTransaction();
            cmd.Connection = Connection;
            cmd.Transaction = trans;

            try
            {
                foreach (String str in SqlStrings)
                {
                    cmd.CommandText = str;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch
            {
                success = false;
                trans.Rollback();
            }
            finally
            {
                Close();
            }

            return success;
        }

        /// **************************************************
        /// 执行一组Sql语句
        /// <param name="SqlStrings">Sql语句组</param>
        /// <returns>是否成功</returns>
        /// **************************************************
        public bool ExecuteSQL(ArrayList SqlStrings)
        {
            bool success = true;

            Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = Connection.BeginTransaction();
            cmd.Connection = Connection;
            cmd.Transaction = trans;

            try
            {
                foreach (String str in SqlStrings)
                {
                    cmd.CommandText = str;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch
            {
                success = false;
                trans.Rollback();
            }
            finally
            {
                Close();
            }

            return success;
        }

        /// **************************************************
        /// 在一个数据表中插入一条记录
        /// <param name="TableName">表名</param>
        /// <param name="Cols">
        ///     哈西表，键值为字段名，值为字段值
        /// </param>
        /// <returns>是否成功</returns>
        /// **************************************************
        public bool Insert(String TableName, Hashtable Cols)
        {
            int Count = 0;

            if (Cols.Count <= 0)
            {
                return true;
            }

            String Fields = " (";
            String Values = " Values(";

            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                    Values += ",";
                }
                Fields += "[" + item.Key.ToString() + "]";
                Values += item.Value.ToString();
                Count++;
            }

            Fields += ")";
            Values += ")";

            String SqlString = "Insert into " + TableName + Fields + Values;
            String[] Sqls = { SqlString };

            return ExecuteSQL(Sqls);
        }

        /// **************************************************
        /// 更新一个数据表
        /// <param name="TableName">表名</param>
        /// <param name="Cols">
        ///     哈西表，键值为字段名，值为字段值
        /// </param>
        /// <param name="Where">Where子句</param>
        /// <returns>是否成功</returns>
        /// **************************************************
        public bool Update(String TableName, Hashtable Cols, String Where)
        {
            int Count = 0;

            if (Cols.Count <= 0)
            {
                return true;
            }

            String Fields = " ";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                }
                Fields += "[" + item.Key.ToString() + "]";
                Fields += "='";
                Fields += item.Value.ToString() + "'";
                Count++;
            }
            Fields += " ";

            String SqlString = "Update " + TableName + " Set " + Fields + Where;

            String[] Sqls = { SqlString };

            return ExecuteSQL(Sqls);
        }

        /// **************************************************
        /// 更新一个数据表
        /// <param name="TableName">表名</param>
        /// <param name="Cols">
        ///     哈西表，键值为字段名，值为字段值
        /// </param>
        /// <param name="Where">Where子句</param>
        /// <returns>是否成功</returns>
        /// **************************************************
        public int UpdateTable(String TableName, Hashtable Cols, String Where)
        {
            int Count = 0;

            if (Cols.Count <= 0)
            {
                return -1;
            }

            String Fields = " ";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                }
                Fields += "[" + item.Key.ToString() + "]";
                Fields += "='";
                Fields += item.Value.ToString() + "'";
                Count++;
            }
            Fields += " ";

            String SqlString = "Update " + TableName + " Set " + Fields + Where;

            return ExecuteSQL(SqlString);
        }

        /// ***************************************************
        /// Execute a sql of params
        /// <param>SqlParameter[]</param>
        /// <returns>是否成功</returns>
        /// ***************************************************
        public bool ExecuteSqlParam(string Sql, SqlParameter[] Prams)
        {
            bool success = true;
            Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            cmd.CommandText = Sql;

            try
            {
                foreach (SqlParameter pram in Prams)
                {
                    cmd.Parameters.Add(pram);
                }

                cmd.ExecuteNonQuery();
            }
            catch
            {
                success = false;
            }
            finally
            {
                Close();
            }

            return success;
        }
    }
}
